﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class DAOLop:DataProvider
    {
        public static DataTable getAll(int makhoi)
        {
            ketnoi = Connect();
            string select = "select * from lop where makhoi=@makhoi";
            OleDbDataAdapter da = new OleDbDataAdapter(select, ketnoi);
            OleDbCommand cmd = new OleDbCommand(select, ketnoi);
            cmd.Parameters.AddWithValue("@makhoi", makhoi);
            da.SelectCommand = cmd;

            DataSet ds = new DataSet();
            da.Fill(ds, "lop");
            Disconnect();
            return ds.Tables[0];
        }

        public static bool isDuplicate(DTOLop lop)
        {
            ketnoi = Connect();
            string select = "select * from lop where tenlop=@tenlop";
            OleDbDataAdapter da = new OleDbDataAdapter(select, ketnoi);
            da.SelectCommand.Parameters.AddWithValue("@tenlop", lop.TenLop);
            new OleDbCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds);

            Disconnect();
            return ds.Tables[0].Rows.Count > 0 ? true : false;
        }

        public static void Add(int makhoi, string tenlop)
        {
            ketnoi = Connect();            
            string select = "select * from lop";
            OleDbDataAdapter da = new OleDbDataAdapter(select, ketnoi);
            new OleDbCommandBuilder(da);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source, "lop");

            DataRow dr = ds.Tables[0].NewRow();
            dr["makhoi"] = makhoi;
            dr["tenlop"] = tenlop;
            dr["siso"] = 0;
            ds.Tables[0].Rows.Add(dr);
           
            da.Update(ds, "lop");
            Disconnect();
        }

        public static void Edit(int malop, int makhoi, string tenlop)
        {
            ketnoi = Connect();
            string select = "select * from lop";
            OleDbDataAdapter da = new OleDbDataAdapter(select, ketnoi);
            new OleDbCommandBuilder(da);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source, "lop");
            da.Fill(ds, "lop");

            DataRow dr = ds.Tables[0].Rows.Find(malop);
            dr.BeginEdit();
            dr["makhoi"] = makhoi;
            dr["tenlop"] = tenlop;
            dr["malop"] = malop;
            dr.EndEdit();

            da.Update(ds, "lop");
            Disconnect();            
        }
       
        public static void Delete(int malop)
        {
            ketnoi = Connect();
            string select = "select * from lop";
            OleDbDataAdapter da = new OleDbDataAdapter(select, ketnoi);
            new OleDbCommandBuilder(da);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source, "lop");
            da.Fill(ds, "lop");

            DataRow dr = ds.Tables[0].Rows.Find(malop);
            dr.Delete();

            da.Update(ds, "lop");
            Disconnect();
        }

        public static int UpdateSiso(int malop,string status)
        {
            OleDbConnection ketNoi = Connect();
            string chuoilenh = "update lop set siso = siso + 1 where malop = @malop";
            if (status == "delete")
            {
                chuoilenh = "update lop set siso = siso - 1 where malop = @malop";
            }
            OleDbCommand lenh = new OleDbCommand(chuoilenh, ketNoi);
            lenh.Parameters.AddWithValue("@malop", malop);
            int result = lenh.ExecuteNonQuery();
            Disconnect();
            return result;
        }
    }
}
